package DGWeb;
import java.lang.reflect.Type;
import java.util.Collection;

import com.google.gson.reflect.TypeToken;

import DGWeb.Model.*;
public interface AppConstants {

	public int MAX_MESSAGES = 10;
	//Collection 
	public final Type MESSAGES_COLLECTION = new TypeToken<Collection<SendMessageToClient>>() {}.getType();
	public final Type COMMENTS_COLLECTION = new TypeToken<Collection<Comments>>() {}.getType();
	public final Type USERS_COLLECTION = new TypeToken<Collection<User>>() {}.getType();
	public final Type FOLLOWERS_COLLECTION = new TypeToken<Collection<Follower>>() {}.getType();
	//Time Format 
	public final String TimeFormat = "yyyy/MM/dd HH:mm:ss";
	//Derby Database
	public final String DERBYDB ="DGWebDB";
	public final String DB_DATASOURCE = "java:comp/env/jdbc/DGWebDB";
	public final String PROTOCOL = "jdbc:derby:"; 
	//Users Table Data
	public final String USERS_TABLE = "USERS";
	public final String USERS_ID = "ID";
	public final String USERNAME ="Username";
	public final String PASSWORD ="Password";
	public final String NICKNAME ="Nickname";
	public final String DESCRIPTION = "Description";
	public final String PHOTO ="PHOTO";
	public final String EMAIL = "EMAIL";
	//Follow Table Data
	public final String FOLLOWER_TABLE = "FOLLOWER";
	public final String FOLLOWER_ID = "ID";
	public final String USER_ID_WHO_FOLLOW = "WHO_FOLLOW_USER";
	public final String USER_ID_FOLLOWER = "FOLLOWER_USER";
	
	//Messages Table Data
	public final String MESSAGES_TABLE = "Messages";
	public final String MESSAGES_ID = "ID";
	public final String MESSAGES_WROTE = "Username";
	public final String MESSAGES_WROTE_NICKNAME = "Nickname";
	public final String MESSAGES_TEXT ="Text";
	public final String MESSAGES_TIME = "Time";
	public final String MESSAGES_TYPE = "Type"; // Message or Republish
	public final String MESSAGES_REPUBLISH_ORIGINAL_MSG = "RepublishMsgID"; // the original Msg ID
	
	//Messages Type
	public final String MESSAGE_TYPE_MESSAGE = "Message";
	public final String MESSAGE_TYPE_REPUBLISH = "Republish";
	public final String MESSAGE_TYPE_REPUBLISH_COMMENT = "CommentRep";
	//Comments
	public final String COMMENTS_TABLE = "Comments";
	public final String COMMENTS_ID = "ID"; 				// primary key
	public final String COMMENTS_MESSAGE_ID="Message_ID"; 	// foreign key
	public final String COMMENTS_TEXT ="Text";
	public final String COMMENTS_TIME ="Time";
	public final String COMMENTS_WHO_WROTE="Nickname";			
	// Topics
	public final String TOPICS_TABLE = "Topics";
	public final String TOPICS_ID = "ID";
	public final String TOPICS_TEXT ="Text";
	public final String TOPICS_MSGID = "MsgID";	
	
	//sql creation of Tables
	public final String USERS_TABLE_CREATE = "CREATE TABLE " + USERS_TABLE +"("+ USERS_ID +" INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"+ USERNAME +" varchar(10) UNIQUE NOT NULL," + PASSWORD+" varchar(8) NOT NULL,"+ NICKNAME +" varchar(20) UNIQUE NOT NULL,"+EMAIL+" varchar(100) UNIQUE NOT NULL," + DESCRIPTION +" varchar(1000)," + PHOTO +" varchar(500))";
	public final String DROP_TABLE_USERS = "DROP TABLE "+USERS_TABLE;
	public final String FOLLOWER_TABLE_CREATE = "CREATE TABLE "+FOLLOWER_TABLE+" ("+FOLLOWER_ID+" INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"+USER_ID_WHO_FOLLOW+ " varchar(10) NOT NULL,"+USER_ID_FOLLOWER+" varchar(10) NOT NULL, FOREIGN KEY ("+USER_ID_WHO_FOLLOW+") REFERENCES "+USERS_TABLE+" ("+USERNAME+"),FOREIGN KEY ("+USER_ID_FOLLOWER+") REFERENCES "+USERS_TABLE+" ("+USERNAME+") )" ;
	public final String DROP_TABLE_FOLLOWER = "DROP TABLE "+FOLLOWER_TABLE;
	public final String MESSAGES_TABLE_CREATE = "CREATE TABLE " + MESSAGES_TABLE +"("+ MESSAGES_ID +" INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"+ MESSAGES_WROTE +" varchar(10) NOT NULL," + MESSAGES_TEXT+" varchar(160) NOT NULL,"+ MESSAGES_TIME +" varchar(40) NOT NULL,"+ MESSAGES_WROTE_NICKNAME+" varchar(10) NOT NULL,"+MESSAGES_TYPE+" varchar(10) NOT NULL,"+MESSAGES_REPUBLISH_ORIGINAL_MSG+" INT, FOREIGN KEY("+MESSAGES_WROTE+") REFERENCES "+USERS_TABLE+" ("+USERNAME+") )";
	public final String DROP_TABLE_MESSAGES = "DROP TABLE "+MESSAGES_TABLE;
	public final String COMMENTS_TABLE_CREATE = "CREATE TABLE "+COMMENTS_TABLE+" ("+COMMENTS_ID+" INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"+COMMENTS_MESSAGE_ID+" INT NOT NULL,"+COMMENTS_WHO_WROTE+" varchar(20) NOT NULL,"+COMMENTS_TEXT+" varchar(140) NOT NULL,"+COMMENTS_TIME+" varchar(40) NOT NULL, FOREIGN KEY ("+COMMENTS_MESSAGE_ID+") REFERENCES "+MESSAGES_TABLE+" ("+MESSAGES_ID+"), FOREIGN KEY ("+COMMENTS_WHO_WROTE+") REFERENCES "+USERS_TABLE+" ("+NICKNAME+") ) ";
	public final String DROP_TABLE_COMMENTS ="DROP TABLE "+COMMENTS_TABLE;
	public final String TOPICS_TABLE_CREATE = "CREATE TABLE "+TOPICS_TABLE+" ("+TOPICS_ID+" INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"+TOPICS_TEXT+" varchar(100) NOT NULL,"+TOPICS_MSGID+" INT NOT NULL, FOREIGN KEY ("+TOPICS_MSGID+") REFERENCES "+MESSAGES_TABLE +" ("+MESSAGES_ID+")  )";
	public final String DROP_TABLE_TOPICS ="DROP TABLE "+ TOPICS_TABLE;
	
	//insert 
	public final String USERS_INSERT = "INSERT INTO " +USERS_TABLE+"("+USERNAME+","+PASSWORD+","+NICKNAME+","+EMAIL+","+DESCRIPTION+","+PHOTO+") VALUES('//','//','//','//','//','//')"; 
	public final String MESSAGES_INSERT ="INSERT INTO " + MESSAGES_TABLE + "("+MESSAGES_WROTE+","+MESSAGES_TEXT+","+MESSAGES_TIME+","+MESSAGES_WROTE_NICKNAME+","+MESSAGES_TYPE+","+MESSAGES_REPUBLISH_ORIGINAL_MSG+") VALUES('//','//','//','//','//',//)";
	public final String FOLLOWER_INSERT = "INSERT INTO "+FOLLOWER_TABLE+"("+USER_ID_WHO_FOLLOW+","+USER_ID_FOLLOWER+") VALUES ('//','//')";
	public final String COMMENTS_INSERT = "INSERT INTO "+COMMENTS_TABLE+" ("+COMMENTS_MESSAGE_ID+","+COMMENTS_WHO_WROTE+","+COMMENTS_TEXT+","+COMMENTS_TIME+") VALUES(//,'//','//','//')";
	public final String TOPICS_INERT = "INSERT INTO "+TOPICS_TABLE+" ("+TOPICS_TEXT+","+TOPICS_MSGID+") VALUES('//',//)";
	//Users Select
	public final String USERS_SELECT_FIND_USER = "SELECT * FROM USERS WHERE Username='//' ";
	public final String USERS_SELECT_FIND_USER_FROM_NICK = "SELECT * FROM USERS WHERE "+NICKNAME+"='//' ";
	//public final String GET_CHECK_IF_NICKNAME_VALID = "SELECT * FROM "+USERS_TABLE+" WHERE "+NICKNAME +"='//'";
	//delete
	public final String FOLLOWER_DELETE = "DELETE FROM "+FOLLOWER_TABLE+" WHERE "+ USER_ID_WHO_FOLLOW+"='//' and "+USER_ID_FOLLOWER+"='//'";
	
	//Follow 
	// Check if the user already Followed
	public final String CHECK_FOLLOW_SELECT = "SELECT * FROM "+FOLLOWER_TABLE+" WHERE "+ USER_ID_WHO_FOLLOW+"='//' and "+USER_ID_FOLLOWER+"='//'";
	public final String CHECK_NUMBER_OF_FOLLOWERS = "SELECT COUNT("+USER_ID_FOLLOWER+") FROM "+ FOLLOWER_TABLE +" WHERE "+USER_ID_FOLLOWER+"='//'";
	public final String CHECK_NUMBER_OF_REPUBLISH = "SELECT COUNT("+MESSAGES_ID+") FROM "+ MESSAGES_TABLE +" WHERE "+MESSAGES_TYPE+"='//' AND "+MESSAGES_REPUBLISH_ORIGINAL_MSG+"=//";
	//get messages
	public final String GET_MESSAGES_SELECT = "SELECT * FROM "+MESSAGES_TABLE+" WHERE "+MESSAGES_WROTE+"<>'//' ORDER BY "+MESSAGES_ID+" DESC";
	public final String GET_MESSAGES_SELECT_FROM_WHO_I_FOLLOW = "SELECT * FROM "+MESSAGES_TABLE+" as m,"+FOLLOWER_TABLE+" as f WHERE m."+MESSAGES_WROTE+"= f."+USER_ID_FOLLOWER+" AND f."+USER_ID_WHO_FOLLOW+"='//' ORDER BY m."+MESSAGES_ID+" DESC";
	public final String GET_MESSAGES_SELECT_OF_ME = "SELECT * FROM "+MESSAGES_TABLE + " WHERE " + MESSAGES_WROTE+"='//'";
	public final String GET_MESSAGE = "SELECT * FROM "+MESSAGES_TABLE +" WHERE "+MESSAGES_ID+"=//";
	public final String GET_MESSAGE_LAST = "SELECT * FROM "+MESSAGES_TABLE+" ORDER BY "+MESSAGES_ID+" DESC FETCH FIRST ROW ONLY";
	public final String GET_MESSAGE_FROM_USER ="SELECT * FROM "+MESSAGES_TABLE+" WHERE "+MESSAGES_WROTE+"='//' ORDER BY "+MESSAGES_ID+" DESC";;
	// get comments
	public final String GET_COMMENTS_SELECT = "SELECT * FROM "+COMMENTS_TABLE+" WHERE "+COMMENTS_MESSAGE_ID+"=// ORDER BY "+COMMENTS_ID+" DESC "; // TBD
	public final String GET_COMMENTS_SELECT_OF_ME = "SELECT * FROM "+COMMENTS_TABLE + " WHERE " + COMMENTS_WHO_WROTE+"='//'";	
	public final String GET_COMMENT_SELECT_BY_ID = "SELECT * FROM "+COMMENTS_TABLE+" WHERE "+COMMENTS_ID+"=// " ;
	
	//get Followers
	public final String GET_FOLLOWERS = "SELECT " + USER_ID_WHO_FOLLOW + " FROM " + FOLLOWER_TABLE +" WHERE "+USER_ID_FOLLOWER+"='//' ";
	public final String GET_FOLLOWERS_WITH_DETAILS = "SELECT USERNAME, NICKNAME, DESCRIPTION, PHOTO  FROM " + FOLLOWER_TABLE +" , "+ USERS_TABLE +" WHERE " + USER_ID_FOLLOWER+"='//' AND " + FOLLOWER_TABLE + "."  + USER_ID_WHO_FOLLOW + "=" + USERS_TABLE + ".USERNAME";
	public final String COUNT_FOLLOWERS = "SELECT COUNT(*) AS num_of_followers FROM " + FOLLOWER_TABLE +" WHERE "+USER_ID_FOLLOWER+"='//'";
	public final String GET_FOLLOWINGS_WITH_DETAILS = "SELECT USERNAME, NICKNAME, DESCRIPTION, PHOTO  FROM " + FOLLOWER_TABLE +" , "+ USERS_TABLE +" WHERE " + USER_ID_WHO_FOLLOW+"='//' AND " + FOLLOWER_TABLE + "."  + USER_ID_FOLLOWER + "=" + USERS_TABLE + ".USERNAME";
	public final String COUNT_FOLLOWINGS = "SELECT COUNT(*) AS num_of_followings FROM " + FOLLOWER_TABLE +" WHERE "+USER_ID_WHO_FOLLOW+"='//'";
	// Topics
	public final String GET_TOPICS_BY_MSGID_AND_TEXT ="SELECT * FROM "+TOPICS_TABLE+" WHERE "+TOPICS_MSGID+"=// AND "+TOPICS_TEXT+"='//'";
	public final String GET_TOPICS_BY_TEXT ="SELECT * FROM "+TOPICS_TABLE+" WHERE "+TOPICS_TEXT+"='//'";
	public final String GET_TOPICS_FROM_MESSAGES_TABLE ="SELECT M.ID,M.Username,M.Nickname,M.Time,M.Text FROM "+MESSAGES_TABLE+" AS M,"+TOPICS_TABLE+" AS T WHERE T."+TOPICS_TEXT+"='//' AND T."+TOPICS_MSGID+"=M."+MESSAGES_ID +" ORDER BY "+MESSAGES_ID+" DESC";

}
